package com.zy.explore.client.infrastructure.plugins.util;

import org.apache.commons.lang3.StringUtils;

import java.io.BufferedReader;
import java.io.CharArrayWriter;
import java.io.File;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 建类说明：mysql 转 oracle 工具类
 *
 * @author 中国印
 * @date 2022年02月17日 00:44:15
 */
public class MySqlToOracleUtil {

    private final static String ORACLE_SQL = "    <!-- ====================================== ↓↓↓↓↓↓ oracle ↓↓↓↓↓↓  ====================================== -->";


    public static void main(String[] args) {
        // TODO 这里替换为自己的项目路径
        String path = System.getProperty("user.dir") + "/zy-db-demo-provider/src/main/java/com/zy/explore/db/domain/mybatis/mapper";
        // String path = "D:\\work\\tl\\tl-szda\\da-alpha-v2.1.3\\src\\main\\resources\\mapping\\modules";
        Map<String, File> fileMap = new HashMap<>();
        getAllFileByRecursion(fileMap, new File(path));
        // getAllFileByRecursion(fileMap, new File("D:\\work\\tl\\tl-szda"));
        System.out.println("xml文件总数：" + fileMap.size());

        fileMap.forEach((filename, xmlFile) -> {
            String filePath = xmlFile.getPath();
            if (filename.equals("MpExampleDao.xml")) {
                System.out.println(filePath);
                try {
                    // 1、加入 databaseId="mysql"
                    addMysql(filePath);
                    // 2、复制一份oracle的sql
                    if (!checkHasOracle(filePath)) {
                        copyMysqlToOracle(filePath);
                    }
                    // 3、加入 databaseId="oracle"
                    addOracle(filePath);
                    // 4、替换mybatis `xxxMapper.xml` 中的sql语句
                    replaceSql(filePath);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        });
        System.out.println(fileMap);
    }

    /**
     * 替换mybatis `xxxMapper.xml` 中的sql语句
     */
    private static void replaceSql(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line;
        int row = 0;
        int rowOracle = 0;
        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains(ORACLE_SQL)) {
                rowOracle = row;
            }

            if (rowOracle != 0 && row > rowOracle) {
                // 转换每一行
                // ① 替换 `LIMIT` -> `AND ROWNUM &lt;= 1` TODO 【注： 部分包含`ORDER BY` 关键字，需单独处理】
                if (line.contains("limit") || line.contains("LIMIT")) {
                    System.out.println();
                    System.out.println(" ==============================↓↓↓↓↓↓  copy分页所需 (" + row + ")  ↓↓↓↓↓↓===================================== ");
                    System.out.println("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (");
                    System.out.println();
                    System.out.println(") TMP  WHERE ROWNUM &lt;=1) WHERE ROW_ID > 0");
                    System.out.println();
                }
                line = StringUtils.replace(line, "limit 1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "LIMIT 1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "limit 0,1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "LIMIT 0,1", "AND ROWNUM &lt;= 1");

                // 需手动处理的SQL 【 group by | 批量插入 | ... 】

                line = transformMysqlToOracle(line);
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * <p>方法说明：将每一行mysql的sql语句转换成oracle的语句</p>
     *
     * @param line 一行字符串
     * @return {@link String }
     */
    public static String transformMysqlToOracle(String line) {

        // ② oracle中不能使用“ ` ”符号
        line = StringUtils.replace(line, "`", "");

        // ③ CONCAT('%', #{name}, '%')  ->     '%'||#{name}||'%' （Oracle中concat函数只能放两个参数）
        if (line.contains("concat")) {
            // String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);
            line = line.replaceAll(",", " || ");
            line = line.replaceAll("concat", "");
        }
        if (line.contains("CONCAT")) {
            // String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);
            line = line.replaceAll(",", " || ");
            line = line.replaceAll("CONCAT", "");
        }

        // ④ `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date类型时间转10位时间戳
        line = line.replaceAll("UNIX_TIMESTAMP", "ORACLE_TO_UNIX");

        // ⑤ 部分关键字需加上双引号 TODO 【注： 字段名大写，映射的别名需保存原本小写！】 `level -> "LEVEL"`  `user -> "USER"`      `number -> "NUMBER"`  `desc -> "DESC"`
        List<String> keywordList = new ArrayList<>(Arrays.asList("level", "user", "number"));
        if (!line.contains("test=")) {
            for (String e : keywordList) {
                // StringUtils.swapCase(e) : 大小写互换
                line = line.replaceAll(" " + e + " ", " \"" + StringUtils.swapCase(e) + "\" ");
                line = line.replaceAll("." + e + " ", "\\.\"" + StringUtils.swapCase(e) + "\" ");
                if (line.endsWith(e) || line.endsWith(e + ",")) {
                    line = line.replaceAll(e, "\"" + StringUtils.swapCase(e) + "\"");
                }
            }
        }
        if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {
            line = line.replaceAll(" date", " \"date\"");
            line = line.replaceAll(" date,", " \"date,\"");
            line = line.replaceAll(" 'date'", " \"date\"");
            line = line.replaceAll(" 'DATE'", " \"date\"");
            line = line.replaceAll(" DATE", " \"date\"");
        }
        line = line.replaceAll(" date ", " \"date\" ");
        line = line.replaceAll(" DATE ", " \"date\" ");

        // ⑥ `IFNULL` -> `NVL`
        line = line.replaceAll("IFNULL", "NVL");
        line = line.replaceAll("ifnull", "NVL");

        // ⑦ 时间 `str_to_date` -> `to_date`     `date_format` -> `to_char`
        // `%Y-%m-%d`  -> `yyyy-MM-dd`    `%Y-%m` -> `yyyy-MM`
        line = line.replaceAll("str_to_date", "TO_DATE");
        line = line.replaceAll("STR_TO_DATE", "TO_DATE");
        line = line.replaceAll("date_format", "TO_CHAR");
        line = line.replaceAll("DATE_FORMAT", "TO_CHAR");

        // 这里注意替换顺序问题，最长的应该放最前面！！！
        line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");
        line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");
        line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");
        line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");
        line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");
        line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");
        line = line.replaceAll("%Y-%m", "yyyy-MM");
        line = line.replaceAll("%Y", "yyyy");
        line = line.replaceAll("%H", "HH24");
        line = line.replaceAll("%k", "HH24");

        line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
        line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");

        // ⑧ ...

        // 需手动处理的SQL 【 group by | 批量插入 | ... 】
        return line;
    }

    /**
     * 加入 databaseId="mysql"
     */
    private static void addMysql(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line;
        while ((line = bufIn.readLine()) != null) {
            boolean lineNeedTransform = line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete");
            boolean lineDoNotNeedTransform = line.contains("databaseId");
            if (lineNeedTransform && !lineDoNotNeedTransform) {
                if (line.endsWith(">")) {
                    line = line.replaceAll(">", " databaseId=\"mysql\">");
                } else {
                    line = line + " databaseId=\"mysql\"";
                }
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 加入 databaseId="oracle"
     */
    private static void addOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        HashSet<String> lineSet = new HashSet<>();
        // 替换
        String line;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains("databaseId=\"mysql\"")) {
                if (lineSet.contains(line)) {
                    line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"oracle\"");
                }
                lineSet.add(line);
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 复制一份oracle的sql
     */
    private static void copyMysqlToOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line;
        // 需要替换的行
        List<String> lineList = new LinkedList<>();

        int row = 0;
        int firstRow = 0;

        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) {
                firstRow = row;
            }

            // 添加替换内容
            if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>")) {
                lineList.add(line);
            }

            // 查询结束位置
            if (line.contains("</mapper>")) {
                tempStream.append(System.getProperty("line.separator"));
                tempStream.write(ORACLE_SQL);
                tempStream.append(System.getProperty("line.separator"));
                tempStream.append(System.getProperty("line.separator"));

                lineList.forEach(lineValue -> {
                    // copy mysql 语句 转为oracle
                    try {
                        tempStream.write(lineValue);
                        tempStream.append(System.getProperty("line.separator"));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                });

                tempStream.append(System.getProperty("line.separator"));
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * 检查是否已经复制SQL
     */
    private static boolean checkHasOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // 内存流, 作为临时流
        CharArrayWriter tempStream = new CharArrayWriter();
        // 替换
        String line;
        boolean result = false;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains(ORACLE_SQL)) {
                result = true;
            }
            // 将该行写入内存
            tempStream.write(line);
            // 添加换行符
            tempStream.append(System.getProperty("line.separator"));
        }
        // 关闭 输入流
        bufIn.close();
        // 将内存中的流 写入 文件
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
        return result;
    }

    /**
     * 递归文件夹 -> 找到所有xml文件
     */
    private static void getAllFileByRecursion(Map<String, File> fileMap, File file) {
        if (!file.exists() || file.isFile()) {
            return;
        }
        File[] fs = file.listFiles();
        if (fs == null) {
            return;
        }
        for (File f : fs) {
            String fileName = f.getName();
            if (f.isDirectory()) {
                // 若是目录则递归，否则打印该目录下的文件
                getAllFileByRecursion(fileMap, f);
            }
            if (f.isFile() && fileName.endsWith("Dao.xml") || fileName.endsWith("Mapper.xml")) {
                fileMap.put(fileName, f);
            }
        }
    }

}
